#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive

#昨天
if [[ $1 == "" ]];then
   TD_DATE=`date -d '1 days ago' "+%Y-%m-%d"`
else
   TD_DATE=$1
fi

TD_DATE_YEAR=`date --date="$TD_DATE" +%Y`

${HIVE_HOME} -S -e "
--总累计和等于前天的累计和加上昨天的数据
insert overwrite table hz03_dlp_zx_dm.zx_dm partition(dt)
--总意向量
select
    count(distinct customer_id) as customer_nums,
    0 as clue_nums,
    null as time_type ,
    null as yearinfo ,
    null as monthinfo,
    null as dayinfo,
    null as hourinfo ,
    null as origin_type_state,
    null as clue_state_stat,
    'all' as group_type,
    null as area,
    null as itcast_subject_id,
    null as subject_name,
    null as itcast_school_id ,
    null as school_name,
    null as tdepart_id,
    null as tdepart_name,
    '${DT_DATE}' as dt
from hz03_dlp_zx_dwd.fact_customer_relationship

union all
-- 线索总量
select
    0 as customer_nums,
    count(distinct id) as clue_nums,
    null as time_type ,
    null as yearinfo ,
    null as monthinfo,
    null as dayinfo,
    null as hourinfo ,
    null as origin_type_state,
    null as clue_state_stat,
    'all' as group_type,
    null as area,
    null as itcast_subject_id,
    null as subject_name,
    null as itcast_school_id ,
    null as school_name,
    null as tdepart_id,
    null as tdepart_name,
    '${DT_DATE}' as dt
from hz03_dlp_zx_dwb.customer_clue_dwb

union all

--拉取今年1号起的数据重新计算
select
    count(distinct cr.customer_id) as customer_nums,
    0 as clue_nums,
    case when grouping__id in(63,127,447,1599,6207)
        then 'hour'
        when grouping__id in(55,119,439,1591,6199)
        then 'day'
        when grouping__id in(51,115,435,1587,6195)
        then 'month'
        when grouping__id in(49,113,433,1585,6193)
        then 'year'
    end time_type,
    cr.yearinfo,
    cr.monthinfo,
    cr.dayinfo,
    cr.hourinfo,
    cr.origin_type_state,
    cc.clue_state_stat,
    case when grouping__id in(113,115,119,127)
        then 'c.area'
        when grouping__id in(433,435,439,337)
        then 'subject'
        when grouping__id in(1585,1587,1591,1599)
        then 'school'
        when grouping__id in(6193,6195,6199,6207)
        then 'tdepart'
    end group_type,
    c.area,
    cr.itcast_subject_id,
    sub.name as subject_name,
    cr.itcast_school_id,
    sch.name as school_name,
    e.tdepart_id,
    dep.name as tdepart_name,
    '${DT_DATE}' as dt
from hz03_dlp_zx_dwd.fact_customer_relationship cr
left JOIN hz03_dlp_zx_dwd.fact_customer_clue cc on cr.id=cc.customer_relationship_id and cc.end_date='9999-99-99'
left JOIN hz03_dlp_zx_dwd.dim_customer c on c.id=cr.customer_id
left join hz03_dlp_zx_dwd.dim_itcast_subject sub on sub.id=cr.itcast_subject_id
left join hz03_dlp_zx_dwd.dim_itcast_school sch on sch.id=cr.itcast_school_id
left join hz03_dlp_zx_dwd.dim_employee e on e.id=cc.creator
left join hz03_dlp_zx_dwd.dim_scrm_department dep on dep.id=e.tdepart_id
where cc.end_date='9999-99-99' and dayinfo >= '${TD_DATE_YEAR}-01-01'
group by yearinfo,monthinfo,dayinfo,hourinfo,origin_type_state,clue_state_stat,
        area,itcast_subject_id,subject_name,itcast_school_id,school_name,tdepart_id,tdepart_name
grouping sets(
--年,线上,线下
(yearinfo,origin_type_state,clue_state_stat), --49
(yearinfo,origin_type_state,clue_state_stat,area), --113
(yearinfo,origin_type_state,clue_state_stat,itcast_subject_id,subject_name), --433
(yearinfo,origin_type_state,clue_state_stat,itcast_school_id,school_name), --1585
(yearinfo,origin_type_state,clue_state_stat,tdepart_id,tdepart_name), --6193
--月,线上,线下
(monthinfo,yearinfo,origin_type_state,clue_state_stat), --51
(monthinfo,yearinfo,origin_type_state,clue_state_stat,area), --115
(monthinfo,yearinfo,origin_type_state,clue_state_stat,itcast_subject_id,subject_name), --435
(monthinfo,yearinfo,origin_type_state,clue_state_stat,itcast_school_id,school_name), --1587
(monthinfo,yearinfo,origin_type_state,clue_state_stat,tdepart_id,tdepart_name), --6195
--日,线上线下
(dayinfo,monthinfo,yearinfo,origin_type_state,clue_state_stat), --55
(dayinfo,monthinfo,yearinfo,origin_type_state,clue_state_stat,area), --119
(dayinfo,monthinfo,yearinfo,origin_type_state,clue_state_stat,itcast_subject_id,subject_name), --439
(dayinfo,monthinfo,yearinfo,origin_type_state,clue_state_stat,itcast_school_id,school_name),  --1591
(dayinfo,monthinfo,yearinfo,origin_type_state,clue_state_stat,tdepart_id,tdepart_name), --6199
--小时,线上线下
(hourinfo,dayinfo,monthinfo,yearinfo,origin_type_state,clue_state_stat), --63
(hourinfo,dayinfo,monthinfo,yearinfo,origin_type_state,clue_state_stat,area), --127
(hourinfo,dayinfo,monthinfo,yearinfo,origin_type_state,clue_state_stat,itcast_subject_id,subject_name), --447
(hourinfo,dayinfo,monthinfo,yearinfo,origin_type_state,clue_state_stat,itcast_school_id,school_name), --1599
(hourinfo,dayinfo,monthinfo,yearinfo,origin_type_state,clue_state_stat,tdepart_id,tdepart_name) --6207
)
union all
select
    0 as customer_nums,
    count(distinct clue_id) as clue_nums,
    case when grouping__id =63
        then 'hour'
        when grouping__id =55
        then 'day'
        when grouping__id =51
        then 'month'
        when grouping__id =49
        then 'year'
    end time_type,
    yearinfo,
    monthinfo,
    dayinfo,
    hourinfo,
    origin_type_state,
    clue_state_stat,
    null as group_type,
    null as area,
    null as itcast_subject_id,
    null as subject_name,
    null as itcast_school_id,
    null as school_name,
    null as tdepart_id,
    null as tdepart_name,
    ${DT_DATE} as dt
from hz03_dlp_zx_dwb.customer_clue_dwb cc
left join hz03_dlp_zx_dwd.fact_customer_relationship cr
on cr.id=cc.customer_relationship_id and cr.end_date='9999-99-99'
where cc.end_date='9999-99-99' and dayinfo >= '${TD_DATE_YEAR}-01-01'
group by yearinfo,monthinfo,dayinfo,hourinfo,origin_type_state,clue_state_stat
grouping sets(
(yearinfo,origin_type_state,clue_state_stat),  --49
(monthinfo,yearinfo,origin_type_state,clue_state_stat), --51
(dayinfo,monthinfo,yearinfo,origin_type_state,clue_state_stat), --55
(hourinfo,dayinfo,monthinfo,yearinfo,origin_type_state,clue_state_stat) --63
);
"